<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    
    
    <title>mysql之慢查询 | 追光者的部落 | 种一棵树最好的时间是十年之前,其次是现在</title>
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
    
    <meta name="keywords" content="MySQL">
    <link rel="shortcut icon" href="/img/favicon.ico">
    <link rel="stylesheet" href="/css/style.css?v=1.0.0">
    
    <script type="text/javascript">
        // Data Center
        var DC = {
            reward:	true,
            lv: JSON.parse('{"enable":false,"app_id":null,"app_key":null,"icon":true}'),
            v: JSON.parse('{"enable":true,"appid":"OCT05YTTqCrexJcyc5Ay0XXD-gzGzoHsz","appkey":"L6b0lDuLhBWOEUhEYmfA7g7C","notify":false,"verify":false,"placeholder":"suggest","avatar":"wavatar"}'),
            g: JSON.parse('{"enable":false,"lazy":true,"owner":"longmartin","repo":null,"oauth":{"client_id":null,"client_secret":null},"perPage":10}'),
            d: JSON.parse('{"app_id":null}')
        };
    </script>
    <script type="text/javascript">
        window.lazyScripts=[];
    </script>
    
</head>


<body>
    <div id="loading" class="active"></div>

    <aside id="menu" class="hide" >
  <div class="inner flex-row-vertical">
    <a href="javascript:;" class="header-icon waves-effect waves-circle waves-light" id="menu-off">
        <i class="icon icon-lg icon-close"></i>
    </a>
    <div class="brand-wrap">
      
      <img src="/img/brand.jpg" class="brand-bg">
      
      <div class="brand">
        <a href="/" class="avatar waves-effect waves-circle waves-light">
          <img src="/img/avatar.jpg">
        </a>
        <hgroup class="introduce">
          <h5 class="nickname">Martin Long</h5>
          <a href="mailto:1156341485@qq.com" title="1156341485@qq.com" class="mail">
            
              <span>1</span>
            
              <span>1</span>
            
              <span>5</span>
            
              <span>6</span>
            
              <span>3</span>
            
              <span>4</span>
            
              <span>1</span>
            
              <span>4</span>
            
              <span>8</span>
            
              <span>5</span>
            
              <span>@</span>
            
              <span>q</span>
            
              <span>q</span>
            
              <span>.</span>
            
              <span>c</span>
            
              <span>o</span>
            
              <span>m</span>
            
          </a>
        </hgroup>
        
        <ul class="menu-link">
          
              <li>
                <a href="https://github.com/longmartin" target="_blank">
                  <i class="icon icon-lg icon-github"></i>
                </a>
              </li>
            
        </ul>
        
      </div>
    </div>
    <div class="scroll-wrap flex-col">
      <ul class="nav">
        
            <li class="">
              <a href="/"  >
                <i class="icon icon-lg icon-home"></i>
                HOME
              </a>
            </li>
        
            <li class="">
              <a href="/categories"  >
                <i class="icon icon-lg icon-th-list"></i>
                分类
              </a>
            </li>
        
            <li class="">
              <a href="/tags"  >
                <i class="icon icon-lg icon-tags"></i>
                标签
              </a>
            </li>
        
            <li class="">
              <a href="/archives"  >
                <i class="icon icon-lg icon-archives"></i>
                文档
              </a>
            </li>
        
            <li class="">
              <a href="https://github.com/longmartin" target="_blank" >
                <i class="icon icon-lg icon-github"></i>
                Github
              </a>
            </li>
        
            <li class="">
              <a href="https://www.jianshu.com/u/696184ca9e21" target="_blank" >
                <i class="icon icon-lg icon-link"></i>
                简书
              </a>
            </li>
        
      </ul>
    </div>
  </div>
</aside>

    <main id="main">
        <header class="top-header" id="header">
    <div class="flex-row clearfix">
        <a href="javascript:;" class="header-icon pull-left waves-effect waves-circle waves-light on" id="menu-toggle">
          <i class="icon icon-lg icon-navicon"></i>
        </a>
        <div class="flex-col header-title ellipsis">
            <span>mysql之慢查询</span>
            
        </div>
        
        <a href="javascript:;" id="site_search_btn" class="header-icon pull-right waves-effect waves-circle waves-light">
            <i class="icon icon-lg icon-search"></i>
        </a>
        
    </div>
</header>
<header class="content-header post-header">
    <div class="container fade-scale">
        <h1 class="title">mysql之慢查询</h1>
        <h5 class="subtitle">
            
                <time datetime="2018-06-17T09:13:08.000Z" itemprop="datePublished" class="page-time">
  2018-06-17
</time>


	<ul class="article-category-list"><li class="article-category-list-item"><a class="article-category-list-link" href="/categories/server/">后台</a></li></ul>

            
        </h5>
        
    </div>
    

</header>

<div id="site_search">
    <div class="search-title clearfix">
        <span class="pull-left">
          <i class="icon icon-lg icon-search"></i>
        </span>
        <input type="text" id="local-search-input" name="q" results="0" placeholder="search my blog..." class="form-control pull-left"/>
        <a href="javascript:;" class="close pull-right waves-effect waves-circle waves-light">
          <i class="icon icon-lg icon-close"></i>
        </a>
    </div>
    <div id="local-search-result"></div>
</div>


<div class="container body-wrap">
    <article id="mysql-20180617-3"
  class="post-article article-type-mysql" itemprop="blogPost">
    <div class="post-card">
        <h1 class="post-card-title">mysql之慢查询</h1>
        <div class="post-meta">
            <time class="post-time" title="2018-06-17 09:13:08" datetime="2018-06-17T09:13:08.000Z"  itemprop="datePublished">2018-06-17</time>

            
	<ul class="article-category-list"><li class="article-category-list-item"><a class="article-category-list-link" href="/categories/server/">后台</a></li></ul>



            

            
    <span class="leancloud-comment">
        <i class="icon icon-comment-o"></i>
        <a href="/server/20180617-3.html#comment">
            <span class="valine-comment-count" data-xid="/server/20180617-3.html"></span>
        </a>
    </span>



            
        </div>
        <div class="post-content" id="post-content" itemprop="postContent">
            
            <h4 id="定位慢查询sql语句"><a href="#定位慢查询sql语句" class="headerlink" title="定位慢查询sql语句"></a>定位慢查询sql语句</h4><p>可以通过开启慢查询来将所有的慢查询记录到某个文件里面，这里以slow-query.log为例</p>
<h6 id="方式一-通过工具分析"><a href="#方式一-通过工具分析" class="headerlink" title="方式一:通过工具分析"></a>方式一:通过工具分析</h6><p>MySQL自带了mysqldumpslow工具用来分析slow query日志，除此之外，还有一些好用的开源工具。比如MyProfi、mysql-log-filter，当然还有mysqlsla等</p>
<p>以下是mysqldumpslow常用参数说明，详细的可应用mysqldumpslow -help查询。</p>
<ul>
<li>-s，是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序（从大到小），ac、at、al、ar表示相应的倒叙。 </li>
<li>-t，是top n的意思，即为返回前面多少条数据。</li>
<li>-g，后边可以写一个正则匹配模式，大小写不敏感。</li>
</ul>
<p>接下来就是用mysql自带的慢查询工具mysqldumpslow分析了（mysql的bin目录下），我这里的日志文件名字是slow-query.log。</p>
<p>列出记录次数最多的10个sql语句<br><figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">mysqldumpslow -s c -t 10 slow-query.log</span><br></pre></td></tr></table></figure></p>
<p>列出返回记录集最多的10个sql语句<br><figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">mysqldumpslow -s r -t 10 slow-query.log</span><br></pre></td></tr></table></figure></p>
<p>按照时间返回前10条里面含有左连接的sql语句<br><figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">mysqldumpslow -s t -t 10 -g &quot;left join&quot; slow-query.log</span><br></pre></td></tr></table></figure></p>
<p>使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化起到非常大的帮助。</p>
<h6 id="方式二-直接分析mysql慢查询日志"><a href="#方式二-直接分析mysql慢查询日志" class="headerlink" title="方式二:直接分析mysql慢查询日志"></a>方式二:直接分析mysql慢查询日志</h6><figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">Time                 Id Command    Argument</span><br><span class="line"># Time: 180419 10:17:15</span><br><span class="line"># User@Host: root[root] @ localhost [::1]</span><br><span class="line"># Thread_id: 2  Schema:   QC_hit: No</span><br><span class="line"># Query_time: 3.018396  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0</span><br><span class="line"># Rows_affected: 0</span><br><span class="line">SET timestamp=1524104235;</span><br><span class="line">SELECT `h_room_item`.`id`, `h_room_item`.`num`, `h_room_item`.`code`, `h_order`.`ord_no`, `h_order`.`stat`, `h_order`.`sex`, `h_order`.`end_dat`, `h_order`.`start_dat`, `h_item_flag`.`flag_id` FROM `h_room_item` LEFT JOIN `h_item_flag` ON h_item_flag.room_id=h_room_item.id LEFT JOIN `h_order` ON h_order.room_no=h_room_item.num and h_order.code=h_room_item.code WHERE (`h_room_item`.`code`=&apos;qt001&apos;) AND (`h_item_flag`.`flag_id` IN (&apos;5&apos;, &apos;6&apos;, &apos;7&apos;, &apos;9&apos;, &apos;17&apos;, &apos;18&apos;, &apos;19&apos;, &apos;20&apos;, &apos;21&apos;))  ORDER BY `h_room_item`.`id` LIMIT 24;</span><br></pre></td></tr></table></figure>
<p>使用desc或者explain来分析sql语句<br><figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">desc SELECT `h_room_item`.`id`, `h_room_item`.`num`, `h_room_item`.`code`, </span><br><span class="line">`h_order`.`ord_no`, `h_order`.`stat`, `h_order`.`sex`, `h_order`.`end_dat`, </span><br><span class="line">`h_order`.`start_dat`, `h_item_flag`.`flag_id` FROM `h_room_item` LEFT JOIN </span><br><span class="line">`h_item_flag` ON h_item_flag.room_id=h_room_item.id LEFT JOIN `h_order` ON </span><br><span class="line">h_order.room_no=h_room_item.num and h_order.code=h_room_item.code WHERE </span><br><span class="line">(`h_room_item`.`code`=&apos;qt001&apos;) AND (`h_item_flag`.`flag_id` IN (&apos;5&apos;, &apos;6&apos;, &apos;7&apos;, &apos;9&apos;, &apos;17&apos;, &apos;18&apos;, </span><br><span class="line">&apos;19&apos;, &apos;20&apos;, &apos;21&apos;))  ORDER BY `h_room_item`.`id` LIMIT 24\G;</span><br></pre></td></tr></table></figure></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">*************************** 1. row ***************************</span><br><span class="line">           id: 1</span><br><span class="line">  select_type: SIMPLE</span><br><span class="line">        table: h_item_flag</span><br><span class="line">         type: ALL</span><br><span class="line">possible_keys: NULL</span><br><span class="line">          key: NULL</span><br><span class="line">      key_len: NULL</span><br><span class="line">          ref: NULL</span><br><span class="line">         rows: 28</span><br><span class="line">        Extra: Using where; Using temporary; Using filesort</span><br><span class="line">*************************** 2. row ***************************</span><br><span class="line">           id: 1</span><br><span class="line">  select_type: SIMPLE</span><br><span class="line">        table: h_room_item</span><br><span class="line">         type: eq_ref</span><br><span class="line">possible_keys: PRIMARY,index2</span><br><span class="line">          key: PRIMARY</span><br><span class="line">      key_len: 4</span><br><span class="line">          ref: hotel_door.h_item_flag.room_id</span><br><span class="line">         rows: 1</span><br><span class="line">        Extra: Using where</span><br><span class="line">*************************** 3. row ***************************</span><br><span class="line">           id: 1</span><br><span class="line">  select_type: SIMPLE</span><br><span class="line">        table: h_order</span><br><span class="line">         type: ALL</span><br><span class="line">possible_keys: index3</span><br><span class="line">          key: NULL</span><br><span class="line">      key_len: NULL</span><br><span class="line">          ref: NULL</span><br><span class="line">         rows: 10</span><br><span class="line">        Extra: Using where; Using join buffer (flat, BNL join)</span><br><span class="line">3 rows in set (0.60 sec)</span><br></pre></td></tr></table></figure>
<h4 id="可能原因："><a href="#可能原因：" class="headerlink" title="可能原因："></a>可能原因：</h4><ul>
<li>1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷)</li>
<li>2、I/O吞吐量小，形成了瓶颈效应。</li>
<li>3、没有创建计算列导致查询不优化。</li>
<li>4、内存不足</li>
<li>5、网络速度慢</li>
<li>6、查询出的数据量过大(可以采用多次查询，其他的方法降低数据量)</li>
<li>7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷)</li>
<li>8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。</li>
<li>9、返回了不必要的行和列</li>
<li>10、查询语句不好，没有优化</li>
</ul>

        </div>
        
<blockquote class="post-copyright">
    <div class="content">
        
<span class="post-time">
    Last updated: <time datetime="2018-06-17T01:17:08.918Z" itemprop="dateUpdated">2018-06-17 01:17:08</time>
</span><br>


        
        转载注明出处，原文地址：<a href="/server/20180617-3.html" target="_blank" rel="external">http://blog.inbelieve.top/server/20180617-3.html</a>
        
    </div>
    <footer>
        <a href="http://blog.inbelieve.top">
            <img src="/img/avatar.jpg" alt="Martin Long">
            Martin Long
        </a>
    </footer>
</blockquote>

        
            <div class="page-reward">
    <a id="rewardBtn" href="javascript:;" class="page-reward-btn waves-effect waves-circle waves-light">赏</a>
</div>

            
        
        <div class="post-footer">
            
	<ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/mysql/">MySQL</a></li></ul>

            <div class="page-share-wrap">
    

<div class="page-share" id="pageShare">
    <ul class="reset share-icons">
      
      <li>
        <a class="weixin share-sns wxFab" href="javascript:;" data-title="微信">
          <i class="icon icon-weixin"></i>
        </a>
      </li>
      
      <li>
        <a class="qq share-sns" target="_blank" href="http://connect.qq.com/widget/shareqq/index.html?url=http://blog.inbelieve.top/server/20180617-3.html&title=《mysql之慢查询》 — 追光者的部落&source=有些鸟儿天生是没办法被关在笼子里的,因为他的每一片羽毛都闪耀着自由的光辉" data-title=" QQ">
          <i class="icon icon-qq"></i>
        </a>
      </li>
      
    </ul>
 </div>



    <a href="javascript:;" id="shareFab" class="page-share-fab waves-effect waves-circle">
        <i class="icon icon-share-alt icon-lg"></i>
    </a>
</div>

        </div>
        
            
    <div id="comment"></div>



        
    </div>
    
<nav class="post-nav flex-row flex-justify-between">
  
    <div class="prev">
      <a href="/frontend/20180617-4.html" id="post-prev" class="post-nav-link">
        <div class="tips"><i class="icon icon-angle-left icon-lg icon-pr"></i> Prev</div>
        <h4 class="title">比较Cookie Session localStorage sessionStorage之区别</h4>
      </a>
    </div>
  

  
    <div class="next">
      <a href="/skill/20180617-1.html" id="post-next" class="post-nav-link">
        <div class="tips">Next <i class="icon icon-angle-right icon-lg icon-pl"></i></div>
        <h4 class="title">慢查询原因总结</h4>
      </a>
    </div>
  
</nav>


    
    
        <aside class="post-widget">
            <nav class="post-toc-wrap" id="post-toc">
                <strong>目录</strong>
                <ol class="post-toc"><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#定位慢查询sql语句"><span class="post-toc-number">1.</span> <span class="post-toc-text">定位慢查询sql语句</span></a></li><li class="post-toc-item post-toc-level-4"><a class="post-toc-link" href="#可能原因："><span class="post-toc-number">2.</span> <span class="post-toc-text">可能原因：</span></a></li></ol>
            </nav>
            <div class="toc-bar"><div>
        </aside>
    
</article>

    <div id="reward" class="page-modal reward-lay">
    <a class="close" href="javascript:;"><i class="icon icon-close"></i></a>
    <h3 class="reward-title">
            请作者喝杯咖啡
    </h3>
    <div class="reward-content">
        
        <div class="reward-code">
            <img id="rewardCode" src="/img/wechat.jpg" alt="打赏二维码">
        </div>
        
        <label class="reward-toggle">
            <input id="rewardToggle" type="checkbox" class="reward-toggle-check"
                data-wechat="/img/wechat.jpg" data-alipay="/img/alipay.jpg">
            <div class="reward-toggle-ctrol">
                <span class="reward-toggle-item wechat">微信</span>
                <span class="reward-toggle-label"></span>
                <span class="reward-toggle-item alipay">支付宝</span>
            </div>
        </label>
        
    </div>
</div>

    
</div>

        <footer class="footer">
    
    <div class="bottom">
        <p>
            <span>
                Martin Long &copy; 2017 - 2018
            </span>
        		
           	
            
            
            <span>
	            Power by <a href="http://hexo.io/" target="_blank">Hexo</a> Theme <a href="https://github.com/codefine/hexo-theme-mellow" target="_blank">book</a>
            </span>
            
            
            
        </p>
    </div>
</footer>

    </main>
    <div class="mask" id="mask"></div>
<a href="javascript:;" id="gotop" class="waves-effect waves-circle waves-light"><span class="icon icon-lg icon-chevron-up"></span></a>



<div class="global-share" id="globalShare">
    <ul class="reset share-icons">
      
      <li>
        <a class="weixin share-sns wxFab" href="javascript:;" data-title="微信">
          <i class="icon icon-weixin"></i>
        </a>
      </li>
      
      <li>
        <a class="qq share-sns" target="_blank" href="http://connect.qq.com/widget/shareqq/index.html?url=http://blog.inbelieve.top/server/20180617-3.html&title=《mysql之慢查询》 — 追光者的部落&source=有些鸟儿天生是没办法被关在笼子里的,因为他的每一片羽毛都闪耀着自由的光辉" data-title=" QQ">
          <i class="icon icon-qq"></i>
        </a>
      </li>
      
    </ul>
 </div>


<div class="page-modal wx-share" id="wxShare">
    <a class="close" href="javascript:;"><i class="icon icon-close"></i></a>
    <p>扫一扫，分享到微信</p>
    <img src="" alt="微信分享二维码">
</div>


    
    <!-- main-js -->
<script type="text/javascript" src="//cdn.bootcss.com/jquery/2.0.0/jquery.min.js"></script>
<script type="text/javascript" src="/js/plugins/fastclick.js?v=1.0.0"></script>

<script type="text/javascript" src="https://cdn.bootcss.com/node-waves/0.7.4/waves.min.js"></script>

<script type="text/javascript" src="/js/method.js?v=1.0.0"></script>
<script type="text/javascript" src="/js/blog.js?v=1.0.0"></script>

<!-- third-party -->





<script type="text/javascript" src="/js/plugins/local_search.js?v=1.0.0"></script>
<script type="text/javascript">
	var search_path = "search.xml";
	if (search_path.length === 0) {
		search_path = "search.xml";
	}
	var path = "/" + search_path;
	searchFunc(path, "local-search-input", "local-search-result");
</script>



    
        <script type="text/javascript" src="https://cdn1.lncld.net/static/js/3.0.4/av-min.js"></script>
<script type="text/javascript" src="//unpkg.com/valine/dist/Valine.min.js"></script>
<script type="text/javascript" src="/js/plugins/valine.js?v=1.0.0"></script>
    
    







    <script>
    (function() {
        var OriginTitile = document.title, titleTime;
        document.addEventListener('visibilitychange', function() {
            if (document.hidden) {
                document.title = 'leaving！';
                clearTimeout(titleTime);
            } else {
                document.title = 'welcome!';
                titleTime = setTimeout(function() {
                    document.title = OriginTitile;
                },2000);
            }
        });
    })();
</script>




    
</body>
</html>
